package com.xqh.study;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;

/**
 * @ClassName LocalJDBCTransApplication
 * @Description jdbc 事务管理
 * @Author xuqianghui
 * @Date 2019/12/28 20:47
 * @Version 1.0
 */
public class LocalJDBCTransApplication2 {

    private static Logger log = LoggerFactory.getLogger(LocalJDBCTransApplication2.class);

    public static void main(String[] args) throws SQLException {

        Connection connection = getConnection();
        connection.setAutoCommit(false);
        /**
         * FOR UPDATE 获取该数据的锁, 如果 数据在另外的事务里未提交,
         * 会阻塞等待事务提交之后 再执行.
         * 如果 不加 唯一索引的where 条件, for update 会锁住整张表.
         */
        String querySql = "SELECT * FROM T_USER FOR UPDATE ";
        PreparedStatement queryStatement = connection.prepareStatement(querySql);
        ResultSet retSet = queryStatement.executeQuery();
        Long superAmount = 0L;
        String superMan = "SuperMan";
        while (retSet.next()){
            String username = retSet.getString(2);
            Long amount = retSet.getLong(3);
            log.info("{} has amount {}", username, amount);
            if(username.equals(superMan)){
                superAmount = amount;
            }
        }

        String plusSql = "UPDATE T_USER set amount = ? where username = ?";
        PreparedStatement ps1 = connection.prepareStatement(plusSql);
        ps1.setLong(1, superAmount);
        ps1.setString(2,superMan);
        ps1.executeUpdate();

        connection.commit();
        ps1.close();
        connection.close();

    }

    public static Connection getConnection() throws SQLException {
        String url = "jdbc:mysql://localhost:3306/dist_trans_course";
        String username = "root";
        String password = "root";
        String driver = "com.mysql.jdbc.Driver";
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            log.error("", e);
        }
        return DriverManager.getConnection(url, username, password);
    }
}
